# __author__ = 'Hoyin'
# __date__ = '2020/06/04'
# __Desc__ = 从数据库中导出数据到excel数据表中

import csv
import sys
import glob
import threading

import openpyxl
import chardet
from DB import DB
from SendEmail import *
from Logger import Logger
from Config import Config
from orders import *

# 文件的根目录
BASE_DIR, filename = os.path.split(os.path.abspath(__file__))
# print(__file__)
BASE_DIR = BASE_DIR.replace('\\', '/')

# 读取配置文件
CONFIG_DIR = sys.argv[1]
CONFIG_FILE = CONFIG_DIR + 'config.ini'
# 调用解析类进行配置文件解析
config = Config(CONFIG_FILE).parse()

threads = []

# 获取参数测试
debug = config.get('debug')

if debug:
    log = Logger(CONFIG_DIR + 'debug.log', level='debug').logger
else:
    log = Logger(CONFIG_DIR + 'error.log', level='error').logger


def readfile(fname):
    """读sql脚本"""
    try:
        # print("开始解析SQL脚本")
        f = open(fname, 'rb')
        data = f.read()
        result = chardet.detect(data)
        data = data.decode(result['encoding'])

        # 过滤注释
        out_sql = []
        da = data.split('\n')
        # print(da)
        for d in da:
            pos = d.find('--')
            if pos != -1:
                # print(pos)
                if pos == 0:
                    d = ''
                else:
                    d = d[:pos]
            out_sql.append(d)
            # print(d)

        if len(out_sql) > 1:
            out_sql = ' '.join(out_sql)
        else:
            out_sql = str(out_sql[0])

        # print("过滤注释以后的sql：" + out_sql)

        # 过滤末尾；号,空行
        out_sql = str(out_sql).replace(';', '').replace('\n', ' ').replace('；', '')
        # print("过滤末尾；以后的sql：" + out_sql)

        # 如果需要换表,则在这里定义换表规则
        if config.get('dynamic_table') == 1:
            out_sql = orders_1(out_sql)
        elif config.get('dynamic_table') == 2:
            out_sql = orders_2(out_sql)
        elif config.get('dynamic_table') == 3:
            out_sql = orders_3(out_sql)
        elif config.get('dynamic_table') == 4:
            out_sql = orders_4(out_sql)
            
        return out_sql

    except Exception as er:
        log.error(er)
        return False


def export_to_excel(sql, server, output_path):
    log.info("开始执行导出到excel")

    results, fields = DB().get_data(server, sql)
    # logging.info(results,fields)

    if not results:
        log.error("导出excel输出错误：{}".format(output_path))
        return False

    wb = openpyxl.Workbook()
    ws = wb.active

    # 写上字段信息
    fields = [f[0] for f in fields]
    # print(fields)
    ws.append(fields)

    # 写入数据
    for res in results:
        try:
            ws.append(res)
        except openpyxl.utils.exceptions.IllegalCharacterError:
            pass

    wb.save(output_path + '.xlsx')
    return True


def export_to_csv(sql, server, output_path):
    if not DB().get_data(server, sql):
        return False
    else:
        results, fields = DB().get_data(server, sql)

    try:
        f = open(output_path + '.csv', 'w+', newline='', encoding='utf-8')
        write = csv.writer(f)

        try:
            head = [x[0] for x in fields]
            write.writerow(head)
            write.writerows(results)
        except Exception as e:
            log.error("写入csv文件失败! 原因：{}".format(e))
        f.close()
    except Exception as e:
        log.error("写入csv文件出错！原因：{}".format(e))

    return True


def bulk_export_to_file(server, sql_files_path):
    # 批量进行导出
    for fp in sql_files_path:

        log.info("开始解析sql脚本:{}".format(fp))

        # 解析sql
        sql = readfile(fp)

        # 保存执行结果
        exports_path = CONFIG_DIR + 'exports/'
        print(exports_path)
        output_file = exports_path + os.path.splitext(os.path.basename(fp))[0]

        if config.get('export_type') == 'excel':
            postfix = 'xls'
            task = threading.Thread(target=export_to_excel, args=(sql, server, output_file))
        elif config.get('export_type') == 'csv':
            postfix = 'csv'
            task = threading.Thread(target=export_to_csv, args=(sql, server, output_file))

        threads.append(task)


def export_to_file(server, sql_files_path):
    # 逐个导出
    for fp in sql_files_path:

        log.info("开始解析sql脚本:{}".format(fp))

        # 解析sql
        sql = readfile(fp)

        # 保存执行结果
        exports_path = CONFIG_DIR + 'exports/'
        print(exports_path)
        output_file = exports_path + os.path.splitext(os.path.basename(fp))[0]

        if config.get('export_type') == 'excel':
            postfix = 'xls'
            result = export_to_excel(sql, server, output_file)
        elif config.get('export_type') == 'csv':
            postfix = 'csv'
            result = export_to_csv(sql, server, output_file)


# db_type
# 1:paas paas库
# 2：posp 收单
# 3：his 收单历史库
# 4：hlw 互联网库
# 5：risk 风控库
# 6：jyx 金盈信库
# 7：wkkj 无卡快捷
# 8：bi  bi库
# 9：paas_new  paas账务
# 10：business  paas账务

# posp 收单库
posp_sql_files = glob.glob(CONFIG_DIR + '/sql/posp/*.*')
if posp_sql_files:
    posp_sql_files = [f.replace('\\', '/') for f in posp_sql_files]
    if config.get('parallel'):
        threads = []
        bulk_export_to_file(2, posp_sql_files)
        for j in threads:
            j.start()

        for k in threads:
            k.join()
    else:
        export_to_file(2, posp_sql_files)

# posp_his 历史库
posp_his_sql_files = glob.glob(CONFIG_DIR + '/sql/his/*.*')
if posp_his_sql_files:
    posp_his_sql_files = [f.replace('\\', '/') for f in posp_his_sql_files]
    if config.get('parallel'):
        threads = []
        bulk_export_to_file(3, posp_his_sql_files)
        for j in threads:
            j.start()

        for k in threads:
            k.join()
    else:
        export_to_file(3, posp_his_sql_files)

# jyx 金盈信
jyx_sql_files = glob.glob(CONFIG_DIR + '/sql/jyx/*.*')
if jyx_sql_files:
    jyx_sql_files = [f.replace('\\', '/') for f in jyx_sql_files]
    if config.get('parallel'):
        threads = []
        bulk_export_to_file(6, jyx_sql_files)
        for j in threads:
            j.start()

        for k in threads:
            k.join()
    else:
        export_to_file(6, jyx_sql_files)

# hlw 互联网库
hlw_sql_files = glob.glob(CONFIG_DIR + '/sql/hlw/*.*')
if hlw_sql_files:
    db_type = 4
    hlw_sql_files = [f.replace('\\', '/') for f in hlw_sql_files]
    if config.get('parallel'):
        threads = []
        bulk_export_to_file(4, hlw_sql_files)
        for j in threads:
            j.start()

        for k in threads:
            k.join()
    else:
        export_to_file(4, hlw_sql_files)

# risk 风控库
risk_sql_files = glob.glob(CONFIG_DIR + '/sql/risk/*.*')
if risk_sql_files:
    db_type = 5
    risk_sql_files = [f.replace('\\', '/') for f in risk_sql_files]
    if config.get('parallel'):
        threads = []
        bulk_export_to_file(5, risk_sql_files)
        for j in threads:
            j.start()

        for k in threads:
            k.join()
    else:
        export_to_file(5, risk_sql_files)

# paas
paas_sql_files = glob.glob(CONFIG_DIR + '/sql/paas/*.*')
if paas_sql_files:
    db_type = 1
    paas_sql_files = [f.replace('\\', '/') for f in paas_sql_files]
    if config.get('parallel'):
        threads = []
        bulk_export_to_file(1, paas_sql_files)
        for j in threads:
            j.start()

        for k in threads:
            k.join()
    else:
        export_to_file(1, paas_sql_files)

# paas_new
paas_new_sql_files = glob.glob(CONFIG_DIR + '/sql/paas_new/*.*')
if paas_new_sql_files:
    db_type = 9
    paas_new_sql_files = [f.replace('\\', '/') for f in paas_new_sql_files]
    if config.get('parallel'):
        threads = []
        bulk_export_to_file(9, paas_new_sql_files)
        for j in threads:
            j.start()

        for k in threads:
            k.join()
    else:
        export_to_file(9, paas_new_sql_files)

# wkkj 无卡快捷
wkkj_sql_files = glob.glob(CONFIG_DIR + '/sql/wkzf/*.*')
if wkkj_sql_files:
    db_type = 7
    wkkj_sql_files = [f.replace('\\', '/') for f in wkkj_sql_files]
    if config.get('parallel'):
        threads = []
        bulk_export_to_file(7, wkkj_sql_files)
        for j in threads:
            j.start()

        for k in threads:
            k.join()
    else:
        export_to_file(7, wkkj_sql_files)

# bi
bi_sql_files = glob.glob(CONFIG_DIR + '/sql/bi/*.*')
if bi_sql_files:
    db_type = 8
    bi_sql_files = [f.replace('\\', '/') for f in bi_sql_files]
    if config.get('parallel'):
        threads = []
        bulk_export_to_file(8, bi_sql_files)
        for j in threads:
            j.start()

        for k in threads:
            k.join()
    else:
        export_to_file(8, bi_sql_files)

# business
business_sql_files = glob.glob(CONFIG_DIR + '/sql/business/*.*')
if bi_sql_files:
    db_type = 10
    business_sql_files = [f.replace('\\', '/') for f in business_sql_files]
    if config.get('parallel'):
        threads = []
        bulk_export_to_file(10, business_sql_files)
        for j in threads:
            j.start()

        for k in threads:
            k.join()
    else:
        export_to_file(10, business_sql_files)

# fxq
fxq_sql_files = glob.glob(CONFIG_DIR + '/sql/fxq/*.*')
if fxq_sql_files:
    db_type = 11
    fxq_sql_files = [f.replace('\\', '/') for f in fxq_sql_files]
    if config.get('parallel'):
        threads = []
        bulk_export_to_file(11, fxq_sql_files)
        for j in threads:
            j.start()

        for k in threads:
            k.join()
    else:
        export_to_file(11, fxq_sql_files)


# 发送邮件
def sendmail(subject, attach_files, receiver):
    # 邮件内容存放路径
    if debug:
        content_path = CONFIG_DIR + 'debug.log'
    else:
        content_path = CONFIG_DIR + 'mail.txt'

    # 传递邮件发送参数
    argvs = {
        'smtp_server': config.get("smtp_server"),
        'port': config.get("port"),
        'user': config.get("user"),
        'passwd': config.get("passwd"),
        'ssl': config.get("ssl"),
        'sender': config.get("sender"),
        'receivers': receiver,
        'subject': subject,
        'content_path': content_path,
        'attach_files': attach_files,
        'attach_title': ''
    }
    # 发送邮件
    send_email = SendEmail(**argvs)
    send_email.send()


#
if config.get('send_to_email'):
    # 邮件主题日期
    nowDate = datetime.datetime.now().strftime('%Y-%m-%d')

    if debug:
        send_title = '[测试邮件]' + config.get('send_title')
    else:
        send_title = '[' + nowDate + ']' + config.get('send_title')

    # 附件路径
    attaches = glob.glob(CONFIG_DIR + '/exports/*.*')

    # 接收邮件列表
    receivers = config.get('send_list').split(',')
    sendmail(send_title, attaches, receivers)

    # 如果定期跑批,则需要清理文件
    try:
        for i in os.listdir(CONFIG_DIR + '/exports/'):
            # print(CONFIG_DIR + '/exports/' + i)
            os.remove(CONFIG_DIR + '/exports/' + i)
            log.info("删除导出文件:%{}".format(CONFIG_DIR + '/exports/' + i))
    except Exception as e:
        print(e)
        log.error(e)
